package org.compiere.process;

import java.awt.Color;
import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.CellFormat;
import jxl.Workbook;
import jxl.biff.FontRecord;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.apache.commons.net.ntp.TimeStamp;
import org.apache.ecs.xhtml.applet;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MLocator;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.jboss.util.propertyeditor.BigDecimalEditor;
import org.joda.time.DateTime;
import org.jpedal.fonts.tt.Loca;

import com.lowagie.text.Font;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class MM_Materials extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	// private Timestamp startDate = null;
	// private Timestamp endDate = null;

	// private String startDate = null;
	// private String endDate = null;
	// private String startDateMinusYear = null;
	// private String endDateMinusYear = null;
	private String getSQlresult = null;
	private Timestamp Period2 = null;
	private Timestamp Period1 = null;
	// private Timestamp tsStartDateMinusYear = null;
	private Timestamp tsEndDateMinusDay = null;
	private BigDecimal Product1 = null;
	private BigDecimal Product2 = null;
	private BigDecimal Werk = null;
	private BigDecimal Locator = null;
	// private String startMaterial2 = null;
	// private String endMaterial2 = null;

	private static CLogger s_log = CLogger.getCLogger(BalanceSheets.class);
	/** */
	private String path = "";

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("Product")) {
				ProcessInfoParameter k = para[i];
				Product1 = (BigDecimal) k.getParameter();
				Product2 = (BigDecimal) k.getParameter_To();
			} else if (name.equals("Werk"))
				Werk = (BigDecimal) para[i].getParameter();
			else if (name.equals("Period")) {
				Period1 = (Timestamp) para[i].getParameter();
				Period2 = (Timestamp) para[i].getParameter_To();
			} else if (name.equals("Locator")) {
				Locator = (BigDecimal) para[i].getParameter();
			} else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		// Calendar c = Calendar.getInstance();
		// c.setTimeInMillis(Period1.getTime());
		//
		//
		//
		// long oneDay = 1 * 24 * 60 * 60 * 1000;
		// tsEndDateMinusDay = new Timestamp(0);
		// if(Period1 != null)
		// tsEndDateMinusDay.setTime(Period1.getTime() - oneDay);
		// else if(Period2 != null)
		// tsEndDateMinusDay.setTime(Period2.getTime() - oneDay);
		//
		// if(Period2 == null)
		// Period2 = Period1;
		//
		// if(Product2 == null)
		// Product2 = Product1;

		path = Util.localFilePath;
	}

	@Override
	protected String doIt() throws Exception {

		Label label;
		Number number;

		String linux = "";

		if (Env.isWindows())
			linux = "";
		else
			linux = "/";

		String templateName = "MM_Materials_tmpl.xls";
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate") + " " + templateName);

		File templateCopy = new File(Util.localFilePath + linux
				+ "MM_Materials.xls");
		File temporaryFile = entry.getFile(Util.localFilePath + linux
				+ templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		try {
			tableBook = Workbook.getWorkbook(temporaryFile);
			copy = Workbook.createWorkbook(templateCopy, tableBook);
			sheet = copy.getSheet(0);
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
			return "Error tableBook.";
		}

		// String org_name;
		// PreparedStatement pstmt = null;
		// ResultSet rs = null;
		// StringBuilder sql = new
		// StringBuilder("select name from ad_org where ad_org_id = 1000000");
		// try {
		// pstmt = DB.prepareStatement(sql.toString(), null);
		// rs = pstmt.executeQuery();
		// while (rs.next()) {
		// org_name = rs.getString(1);
		// WritableFont arial10ptBold = new WritableFont(WritableFont.TIMES, 9,
		// WritableFont.NO_BOLD);
		// WritableCellFormat captionStyle = new
		// WritableCellFormat(arial10ptBold);
		// label = new Label(0, 4, ("Наименование организации: " + org_name),
		// captionStyle);
		// sheet.addCell(label);
		//
		// }
		// } catch (SQLException e) {
		// s_log.log(Level.SEVERE, "Report: MM_Materials", e);
		// getSQlresult = e.getMessage();
		// } finally {
		// DB.close(rs, pstmt);
		// rs = null;
		// pstmt = null;
		// }

		// ExcelCell cellStart = Utils.getCellStart(tableBook,">>");
		// Style for value column
		WritableFont arial10ptBold = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.NO_BOLD);
		WritableCellFormat vsFloat = null;
		vsFloat = new WritableCellFormat(arial10ptBold,
				NumberFormats.THOUSANDS_FLOAT);
		vsFloat.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		vsFloat.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		vsFloat.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsFloat.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		vsFloat.setAlignment(Alignment.RIGHT);
		vsFloat.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsFloat.setWrap(true);
		vsFloat.setShrinkToFit(true);

		WritableFont arial10ptBold2 = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.NO_BOLD);
		WritableCellFormat vsInteter = null;
		vsInteter = new WritableCellFormat(arial10ptBold2,
				NumberFormats.INTEGER);
		vsInteter.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		vsInteter.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		vsInteter.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsInteter.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		vsInteter.setAlignment(Alignment.RIGHT);
		vsInteter.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsInteter.setWrap(true);
		vsInteter.setShrinkToFit(true);

		WritableCellFormat vsLabel = null;
		vsLabel = new WritableCellFormat(arial10ptBold,
				NumberFormats.THOUSANDS_FLOAT);
		vsLabel.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		vsLabel.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		vsLabel.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsLabel.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		vsLabel.setAlignment(Alignment.CENTRE);
		vsLabel.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsLabel.setWrap(true);
		vsLabel.setShrinkToFit(true);

		WritableCellFormat vsr = null;
		vsr = new WritableCellFormat(arial10ptBold,
				NumberFormats.THOUSANDS_FLOAT);
		vsr.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		vsr.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		vsr.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		vsr.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		vsr.setAlignment(Alignment.LEFT);
		vsr.setVerticalAlignment(VerticalAlignment.CENTRE);
		vsr.setWrap(true);
		vsr.setShrinkToFit(true);

		// vsInteter.setBackground(Colour.getInternalColour(44));

		List<line> list = getDatas();
		if (getSQlresult != null)
			return Msg.translate(m_ctx, "Отчет: MM_Materials: Ошибка запроса.");

		Calendar c = Calendar.getInstance();
		c.setTimeInMillis(Period1.getTime());

		String caption = " за период, с "
				+ new java.text.SimpleDateFormat("dd.MM.yyyy").format(
						Period1.getTime()).toString()
				+ " по "
				+ new java.text.SimpleDateFormat("dd.MM.yyyy").format(
						Period2.getTime()).toString();

		// + c.get(Calendar.DAY_OF_MONTH) + " "
		// + org.utils.DataUtils.getMonthNameRU(new
		// Date(Period2.getTime())).toLowerCase()
		// + " " + c.get(Calendar.YEAR) + " года.";

		WritableFont wf = new WritableFont(WritableFont.TIMES, 10,
				WritableFont.BOLD);
		WritableCellFormat captionStyle = new WritableCellFormat(wf);
		captionStyle.setAlignment(Alignment.CENTRE);
		captionStyle.setVerticalAlignment(VerticalAlignment.CENTRE);

		label = new Label(0, 3, caption, captionStyle);
		sheet.addCell(label);

		if (Locator != null && Locator.compareTo(Env.ZERO) == 1){
			MLocator locator = new MLocator(Env.getCtx(), Locator.intValue(), null);
			String s = "Материально-ответственное лицо: " + locator.getValue();
			
			label = new Label(0, 5, s, captionStyle);
			sheet.addCell(label);
			
		}
		
		
		if (list.size() == 0)
			return Msg.translate(m_ctx, "По заданому параметру нет данных");
		// output datas ----------------------------------{
		int row = 10;

		double sOst1 = .0;
		double sOst2 = .0;
		double sprihod1 = .0;
		double sprihod2 = .0;
		double srashod1 = .0;
		double srashod2 = .0;
		double sqty = .0;
		double ssumm = .0;

		for (line ll : list) {

			sheet.insertRow(row);

			double sumTotal = .0;
			number = new Number(0, row, (row - 8), vsInteter);
			sheet.addCell(number);
			number = new Number(2, row, ll.product_id.intValue(), vsInteter);
			sheet.addCell(number);
			label = new Label(3, row, ll.name, vsr);
			sheet.addCell(label);
			label = new Label(4, row, ll.acct, vsLabel);
			sheet.addCell(label);
			label = new Label(5, row, ll.uom, vsLabel);
			sheet.addCell(label);

			sOst1 += getValue(ll.ost1);
			number = new Number(6, row, getValue(ll.ost1), vsFloat);
			sheet.addCell(number);

			double price = .0;
			if (ll.ost1.compareTo(Env.ZERO) == 1
					&& ll.ost2.compareTo(Env.ZERO) == 1)
				price = ll.ost2.doubleValue() / ll.ost1.doubleValue();
			number = new Number(7, row, price, vsFloat);
			sheet.addCell(number);

			sOst2 += getValue(ll.ost2);
			number = new Number(8, row, getValue(ll.ost2), vsFloat);
			sheet.addCell(number);

			sprihod1 += getValue(ll.prihod1);
			number = new Number(9, row, ll.prihod1.doubleValue(), vsFloat);
			sheet.addCell(number);

			sprihod2 += getValue(ll.prihod2);
			number = new Number(10, row, ll.prihod2.doubleValue(), vsFloat);
			sheet.addCell(number);

			srashod1 += getValue(ll.rashod1);
			number = new Number(11, row, ll.rashod1.doubleValue(), vsFloat);
			sheet.addCell(number);

			srashod2 += getValue(ll.rashod2);
			number = new Number(12, row, ll.rashod2.doubleValue(), vsFloat);
			sheet.addCell(number);

			BigDecimal qty = Env.ZERO;
			qty = qty.add(ll.ost1).add(ll.prihod1).subtract(ll.rashod1);
			sqty += getValue(qty);

			BigDecimal summ = Env.ZERO;
			summ = summ.add(ll.ost2).add(ll.prihod2).subtract(ll.rashod2);

			price = .0;
			if (qty.compareTo(Env.ZERO) == 1 && summ.compareTo(Env.ZERO) == 1)
				price = summ.doubleValue() / qty.doubleValue();

			number = new Number(13, row, qty.doubleValue(), vsFloat);
			sheet.addCell(number);

			number = new Number(14, row, price, vsFloat);
			sheet.addCell(number);
			if (qty.compareTo(Env.ZERO) == 0) {
				summ = Env.ZERO;
			}else
				ssumm += getValue(summ);
			
			number = new Number(15, row, summ.doubleValue(), vsFloat);
			sheet.addCell(number);

			// sheet.addCell(number);
			// number = new Number(12, row, sumTotal, vsFloat);
			// sheet.addCell(number);
			//
			int l = 0;
			// if(ll.name.length())// > ll.werk.length())
			l = ll.name.length();
			// else
			// l = ll.werk.length();

			if (l > 30) {
				int diff = l / 30;
				if (diff > 0) {
					int height = 300 * (diff + 1);
					sheet.setRowView(row, height);
				}

			} else
				sheet.setRowView(row, 300);
			// number.set

			row++;
		}

		number = new Number(6, row, sOst1, vsFloat);
		sheet.addCell(number);

		number = new Number(8, row, sOst2, vsFloat);
		sheet.addCell(number);

		number = new Number(9, row, sprihod1, vsFloat);
		sheet.addCell(number);

		number = new Number(10, row, sprihod2, vsFloat);
		sheet.addCell(number);

		number = new Number(11, row, srashod1, vsFloat);
		sheet.addCell(number);

		number = new Number(12, row, srashod2, vsFloat);
		sheet.addCell(number);

		number = new Number(13, row, sqty, vsFloat);
		sheet.addCell(number);

		number = new Number(15, row, ssumm, vsFloat);
		sheet.addCell(number);

		row +=10;
		
		if (Locator != null && Locator.compareTo(Env.ZERO) == 1){
			MLocator locator = new MLocator(Env.getCtx(), Locator.intValue(), null);
			String s = locator.getValue();
			
			captionStyle = new WritableCellFormat(wf);
			captionStyle.setAlignment(Alignment.CENTRE);
			captionStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
			captionStyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
			
			label = new Label(8, row, s, captionStyle);
			sheet.addCell(label);
			
		}
		
		// output datas -----------------------------------------}
		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();

		String file;
		if (Env.isWindows())
			file = Util.localFilePath + linux + "MM_Materials.xls";
		else
			file = Util.localFilePath + linux + "MM_Materials.xls";

		if (!Env.isWindows())
			Util.launchFile(new File(file));
		else {
			try {
				String command = "excel \"" + file + "\"";
				Runtime.getRuntime().exec("cmd /c start " + command);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return Msg.translate(m_ctx, "Success");
	}

	protected double getValue(Object o) {
		double result = .0;

		if (o instanceof BigDecimal) {
			if (o == null)
				return result;
			else
				result = ((BigDecimal) o).doubleValue();
		}

		return result;
	}

	private List<line> getDatas() {
		List<line> list = new ArrayList<line>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		StringBuilder sql = getSQl(Period1, Period2, tsEndDateMinusDay, Werk,
				Product1, Product2);

		try {
			pstmt = DB.prepareStatement(sql.toString(), null);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				int i = 0;
				line l = new line();
				l.product_id = rs.getBigDecimal("value");
				// l.werk = rs.getString("org");
				l.name = rs.getString("name");
				l.acct = rs.getString("acct");
				l.curr = rs.getString("curr");
				l.uom = rs.getString("uom");
				l.ost1 = rs.getBigDecimal("ost");
				if (l.ost1 != null && l.ost1.compareTo(Env.ZERO) != 0)
					l.ost2 = rs.getBigDecimal("ostsumm");
				else
					i++;

				l.prihod1 = rs.getBigDecimal("prihod");
				if (l.prihod1 != null && l.prihod1.compareTo(Env.ZERO) != 0)
					l.prihod2 = rs.getBigDecimal("prihodsumm");
				else
					i++;

				l.rashod1 = rs.getBigDecimal("rashod");
				if (l.rashod1 != null && l.rashod1.compareTo(Env.ZERO) != 0)
					l.rashod2 = rs.getBigDecimal("rashodsumm");
				else
					i++;

				if (i != 3)
					list.add(l);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: MM_Materials", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return list;
	}

	private StringBuilder getSQl(Timestamp Period1, Timestamp Period2,
			Timestamp tsEndDateMinusDay, BigDecimal werk, BigDecimal Product1,
			BigDecimal Product2) {
		StringBuilder sql = new StringBuilder();
		sql.append("select \n"
				+ "	p.value\n"
				+ "	,p.name\n"
				+ "	,(\n"
				+ " select ce.value	\n"
				+ " from adempiere.m_product_acct pp	\n"
				+ " left join adempiere.c_validcombination cv on cv.c_validcombination_id = pp.p_asset_acct	\n"
				+ " left join adempiere.c_elementvalue ce on c_elementvalue_id = cv.account_id	\n"
				+ "		where pp.m_product_id = p.m_product_id \n"
				+ "	) acct\n"
				+ "	,'KZT' curr\n"
				+ "	,(select name from adempiere.c_uom_trl where c_uom_id = p.c_uom_id) as uom\n"
				+
				// ost------------------
				"	,COALESCE((	\n"
				+ "		select sum(mm.qty) qty\n"
				+ "		from (\n"
				+ "		select \n"
				+ "			sum(case when il.inventorytype = 'C' then -(il.qtyinternaluse + il.qtycount) else (il.qtyinternaluse + il.qtycount) end) qty\n"
				+ "			, sum(0) as nn\n"
				+ "		from adempiere.m_inventoryline il \n"
				+ "		inner join adempiere.m_inventory i on i.m_inventory_id = il.m_inventory_id \n"
				+ "		where il.m_product_id = p.m_product_id\n"
				+ "		and i.movementdate < '"  
				+ Period1 + "'\n"
				+ getLocator("il","",Locator)
				
				+

				"		union all\n"
				+ "		select \n"
				+ "			sum(iol.qtyentered), sum(0) as nn	\n"
				+ "		from adempiere.m_inoutline iol \n"
				+ "		inner join adempiere.m_inout io on iol.m_inout_id = io.m_inout_id \n"
				+ "		where p.m_product_id = iol.m_product_id\n"
				+ "		and io.dateacct < '"
				+ Period1 + "'\n"
				+ getLocator("iol","",Locator)
				
				+ "		) mm\n"
				+ "	),0)\n"
				+ "	as ost\n"
				+

				// ostsumm----------------------
				"	,COALESCE((	\n"
				+ "		select sum(mm.summ) summ\n"
				+ "		from (\n"
				+ "		select \n"
				+ "			sum(case when il.inventorytype = 'C' then -f.amtacctcr else f.amtacctcr end) as summ	\n"
				+ "		from adempiere.m_inventoryline il \n"
				+ "		inner join adempiere.m_inventory i on i.m_inventory_id = il.m_inventory_id \n"
				+ "		left join adempiere.fact_acct f on f.record_id = i.m_inventory_id\n"
				+ "		where il.m_product_id = p.m_product_id\n"
				+ "		and i.movementdate < '"
				+ Period1 + "'\n"
				+ getLocator("il","",Locator)
				
				+ "		and f.ad_table_id = 321\n"
				+ "		and f.line_id = il.m_inventoryline_id\n"
				+ "		\n"
				+ "		union all\n"
				+ "		select \n"
				+ "			sum(f.amtacctcr) as summ\n"
				+ "		from adempiere.m_inoutline iol \n"
				+ "		inner join adempiere.m_inout io on iol.m_inout_id = io.m_inout_id \n"
				+ "		left join adempiere.fact_acct f on f.record_id = iol.m_inout_id\n"
				+ "		where p.m_product_id = iol.m_product_id\n"
				+ "		and io.dateacct < '"
				+ Period1+ "'\n"
				+ getLocator("iol","",Locator)
				
				+ "		and f.ad_table_id = 319\n"
				+ "		and f.line_id = iol.m_inoutline_id\n"
				+ "		) mm\n"
				+ "	),0)\n"
				+ "	as ostsumm\n"
				+
				// prihod-----------------------
				"	,COALESCE((	\n"
				+

				"	select sum(mm.qty) qty	\n"
				+ "	from (	\n"
				+ "		select sum(l.movementqty) as qty \n"
				+ "			from adempiere.m_inoutline as l\n"
				+ "			inner join adempiere.m_inout as i on i.m_inout_id = l.m_inout_id\n"
				+ "				where l.m_product_id = p.m_product_id \n"
				+ "				and i.dateacct between '"
				+ Period1
				+ "' and '"
				+ Period2 + "' \n"
				+ getLocator("l","",Locator)
		// +
		//
		// " union all \n " +
		//
		// "		select \n" +
		// "			sum(il.qtyinternaluse + il.qtycount) qty\n" +
		// "		from adempiere.m_inventoryline il \n" +
		// "		inner join adempiere.m_inventory i on i.m_inventory_id = il.m_inventory_id \n"
		// +
		// "		where il.m_product_id = p.m_product_id\n" +
		// "		and i.movementdate between '"+ Period1 +"' and '" + Period2 +
		// "' \n"+
		// "		and il.inventorytype <> 'C'	\n"

		);
		if (Locator != null && Locator.compareTo(Env.ZERO) == 1)
			sql.append("	union all	\n"
					+

					"	select sum(l.movementqty) as qty	\n"
					+ "	from adempiere.m_movementline as l	\n"
					+ "	inner join adempiere.m_movement as i on i.m_movement_id = l.m_movement_id	\n"
					+ "		where l.m_product_id = p.m_product_id	\n"
					+ "		and i.movementdate between '" + Period1 + "' and '"
					+ Period2 + "' \n" + "		" 
					+getLocator("l","to",Locator)
					);
		
		
		sql.append("	)mm	\n"
				+

				"	),0) as prihod\n"
				+

				// prihodsumm ---------------
				"	,COALESCE((	\n"
				+ "	select sum(mm.summ) summ	\n"
				+ "	from (	\n"
				+ "		select sum(f.amtacctdr) as summ\n"
				+ "			from adempiere.m_inoutline as l\n"
				+ "			inner join adempiere.m_inout as i on i.m_inout_id = l.m_inout_id\n"
				+ "			left join adempiere.fact_acct f on f.record_id = l.m_inout_id	\n"
				+ "				where l.m_product_id = p.m_product_id \n"
				+ "				and i.dateacct between '" + Period1 + "' and '"
				+ Period2 + "' \n" + "				and f.ad_table_id = 319\n"
				+ "				and f.line_id = l.m_inoutline_id\n"
				+ getLocator("l","",Locator)
				);

		if (Locator != null && Locator.compareTo(Env.ZERO) == 1)
			sql.append("	union all	\n"
					+

					"	select sum(f.amtacctdr) as summ	\n"
					+ "	from adempiere.m_movementline as l	\n"
					+ "	inner join adempiere.m_movement as i on i.m_movement_id = l.m_movement_id	\n"
					+ "	left join adempiere.fact_acct f on f.record_id = l.m_movement_id	\n"
					+ "		where l.m_product_id = p.m_product_id	\n"
					+ "		and i.movementdate between '" + Period1 + "' and '"
					+ Period2 + "' \n" + "		and f.ad_table_id = 323	\n"
					+ "		and f.line_id = l.m_movementline_id	\n"
					+ getLocator("l","to",Locator)
					);
		sql.append(")mm	\n"
				+ "	),0) as prihodsumm\n"
				+

				// rashod -----------------
				"	,COALESCE((\n"
				+ "	select sum(mm.qty) qty	\n"
				+ "	from (	\n"
				+ "		select \n"
				+ "			sum(il.qtyinternaluse + il.qtycount) qty	\n"
				+ "		from adempiere.m_inventoryline il \n"
				+ "		inner join adempiere.m_inventory i on i.m_inventory_id = il.m_inventory_id \n"
				+ "		where il.m_product_id = p.m_product_id\n"
				+ "		and i.movementdate between '" + Period1 + "' and '"
				+ Period2 + "' \n" + "		and il.inventorytype = 'C'	\n"
				+ getLocator("il","",Locator)
 
				
		);

		
		
		if (Locator != null && Locator.compareTo(Env.ZERO) == 1)
			sql.append(
					"	and il.m_locator_id = " + Locator + 
					"	union all \n"
					+ "	select sum(l.movementqty) as qty \n"
					+ "	from adempiere.m_movementline as l \n"
					+ "	inner join adempiere.m_movement as i on i.m_movement_id = l.m_movement_id \n"
					+ "		where l.m_product_id = p.m_product_id \n"
					+ "		and i.movementdate between '" + Period1 + "' and '"
					+ Period2 + "' \n" 
					+ getLocator("l","",Locator)
					);

		sql.append(")mm \n"
				+

				"	),0)rashod\n"
				+

				//rashodsumm----------------------
				"	,COALESCE((\n"
				+ "	select sum(mm.summ) summ	\n"
				+ "	from (	\n"
				+

				"		select \n"
				+ "			sum(f.amtacctdr) summ\n"
				+ "		from adempiere.m_inventoryline il \n"
				+ "		inner join adempiere.m_inventory i on i.m_inventory_id = il.m_inventory_id \n"
				+ "		left join adempiere.fact_acct f on f.record_id = i.m_inventory_id\n"
				+ "		where il.m_product_id = p.m_product_id\n"
				+ "		and i.movementdate between '" + Period1 + "' and '"
				+ Period2 + "' \n" + "		and f.ad_table_id = 321\n"
				+ "		and f.line_id = il.m_inventoryline_id	\n"
				+ getLocator("il","",Locator)
				);

		if (Locator != null && Locator.compareTo(Env.ZERO) == 1)
			sql.append("	union all	\n"
					+

					"	select sum(f.amtacctdr) as summ	\n"
					+ "	from adempiere.m_movementline as l	\n"
					+ "	inner join adempiere.m_movement as i on i.m_movement_id = l.m_movement_id	\n"
					+ "	left join adempiere.fact_acct f on f.record_id = i.m_movement_id	\n"
					+ "	where l.m_product_id = p.m_product_id	\n"
					+ "	and i.movementdate between '" + Period1 + "' and '"
					+ Period2 + "' \n" + "	and f.ad_table_id = 323	\n"
					+ "	and f.line_id = l.m_movementline_id	\n"
					+ getLocator("l","",Locator)

					);

		sql.append("	)mm	\n"
				+

				"		\n"
				+ "	),0)rashodsumm\n"
				+ "	\n"
				+ "	\n"
				+ "from adempiere.m_product p\n"
				+ "left join adempiere.m_productprice as c on c.m_product_id = p.m_product_id and c.isactive = 'Y'\n"
				+ "where p.ad_org_id = " + werk + "\n"
				+ " and p.isactive = 'Y' \n"

		);

		// sql.append(
		// "select \n" +
		// "	p.m_product_id\n" +
		// "	,p.name\n" +
		// "	,COALESCE(c.pricelist,0) as pricelist \n" +
		// "	,'KZT' curr\n" +
		// "	,(select name from adempiere.c_uom_trl where c_uom_id = p.c_uom_id) as uom\n"
		// +
		// "	,COALESCE((	\n" +
		// "		select sum(mm.qty) qty\n" +
		// "		from (\n" +
		// "		select \n" +
		// "			sum(case when il.inventorytype = 'C' then -(il.qtyinternaluse + il.qtycount) else (il.qtyinternaluse + il.qtycount) end) qty\n"
		// +
		// "		from adempiere.m_inventoryline il \n" +
		// "		inner join adempiere.m_inventory i on i.m_inventory_id = il.m_inventory_id \n"
		// +
		// "		where il.m_product_id = p.m_product_id\n" +
		// "		and i.movementdate < '"+ Period1 +"'\n" +
		// "		union all\n" +
		// "		select \n" +
		// "			sum(iol.qtyentered)	\n" +
		// "		from adempiere.m_inoutline iol \n" +
		// "		inner join adempiere.m_inout io on iol.m_inout_id = io.m_inout_id \n"
		// +
		// "		where p.m_product_id = iol.m_product_id\n" +
		// "		and io.dateacct < '"+ Period1 +"'\n" +
		// // "		-- union all\n" +
		// // "		-- select \n" +
		// // "		-- 	sum(ml.movementqty)	\n" +
		// // "		-- from adempiere.m_product p\n" +
		// //
		// "		-- inner join adempiere.m_movementline ml on ml.m_product_id = p.m_product_id\n"
		// +
		// //
		// "		-- inner join adempiere.m_movement m on m.m_movement_id = ml.m_movement_id \n"
		// +
		// // "		-- where p.m_product_id = 1000008\n" +
		// // "		-- and m.movementdate < '03.03.2013'\n" +
		// "		) mm\n" +
		// "	),0)\n" +
		// "	as ost\n" +
		// "	,COALESCE((	\n" +
		// "		select sum(l.movementqty)\n" +
		// "			from adempiere.m_inoutline as l\n" +
		// "			inner join adempiere.m_inout as i on i.m_inout_id = l.m_inout_id\n"
		// +
		// "				where l.m_product_id = p.m_product_id \n" +
		// "				and i.dateacct between '"+ Period1 +"' and '" + Period2 + "' \n"
		// +
		// "	),0) as prihod\n" +
		// "	,COALESCE((\n" +
		// "		select \n" +
		// "			sum(case when il.inventorytype = 'C' then -(il.qtyinternaluse + il.qtycount) else (il.qtyinternaluse + il.qtycount) end) qty\n"
		// +
		// "		from adempiere.m_inventoryline il \n" +
		// "		inner join adempiere.m_inventory i on i.m_inventory_id = il.m_inventory_id \n"
		// +
		// "		where il.m_product_id = p.m_product_id\n" +
		// "		and i.movementdate between '"+ Period1 +"' and '" + Period2 +
		// "' \n" +
		// "	),0)rashod\n" +
		// "	\n" +
		// "from adempiere.m_product p\n" +
		// "left join adempiere.m_productprice as c on c.m_product_id = p.m_product_id and c.isactive = 'Y'\n"
		// +
		// "where p.ad_org_id = "+werk + "\n" +
		// " and p.isactive = 'Y' \n"
		//
		//
		// );
		if ((Product1 != null && Product1.compareTo(Env.ZERO) == 1)
				&& (Product2 != null && Product2.compareTo(Env.ZERO) == 1))
			sql.append("and p.m_product_id between " + Product1 + " and "
					+ Product2 + " ");
		else if ((Product1 != null && Product1.compareTo(Env.ZERO) == 1)
				&& (Product2 == null || Product2.compareTo(Env.ZERO) != 1))
			sql.append("and p.m_product_id = " + Product1);

		sql.append(" order by p.m_product_id ");

		return sql;
	}

	protected String getLocator(String s,String to, BigDecimal Locator){
		String result = "";
		if (Locator != null && Locator.compareTo(Env.ZERO) == 1)
			result = " and "+s+".m_locator"+to+"_id	= " + Locator;
		return result;
	}

	
	class line {
		BigDecimal product_id = null;
		String werk = null;
		String name = null;
		String acct = null;
		String curr = null;
		String uom = null;

		BigDecimal ost1 = Env.ZERO;
		BigDecimal ost2 = Env.ZERO;
		BigDecimal prihod1 = Env.ZERO;
		BigDecimal prihod2 = Env.ZERO;
		BigDecimal rashod1 = Env.ZERO;
		BigDecimal rashod2 = Env.ZERO;
		BigDecimal ost11 = Env.ZERO;
		BigDecimal ost22 = Env.ZERO;

	}

}